
clear	
set more off
if c(username)=="Scognamillo"	{
gl drive		"C:\Users\Scognamillo\Dropbox\FAO\Epic\Malawi\SPIA adoption\Antonio Scognamillo/IHPS"
gl tmp_panel	"$drive/tmp/josh"								
gl slm_do 		"$drive/panel_creation"
gl maps			"$drive/maps"
gl graphs 		"$drive/graphs"
gl out			"$drive/out"
gl tables		"$drive/tables/raw"
								}
								
else if c(username)=="Antonio"	{
gl drive		"C:\Users\Antonio\Dropbox\FAO\EPIC\malawi\SPIA adoption\Antonio Scognamillo/IHPS"
gl tmp_panel	"$drive/tmp/josh"								
gl slm_do 		"$drive/panel_creation"
gl maps			"$drive/maps"
gl graphs 		"$drive/graphs"
gl out			"$drive/out"
gl tables		"$drive/tables/raw"

}					 			


********************************************************************************
********************************************************************************
********************************************************************************
**********************DESCRIPTIVE STATISTICS ***********************************
********************************************************************************
********************************************************************************
********************************************************************************
********************************************************************************



**********************************CSA
**	Make Table of means at plot level
use "$out/CSA_practices_plot", clear 
qui : svyset psu[pw=panelweight], strata(strata) 

gl practices 	mintill crop_rot intleg_1 covercrop_any mulch  msd_assoc msd_residues assoc_residues cons_ag ///
		tree  intercrop_other  swc_mech swc_bio swc_any fert_org_use 	///
		burn   
	
gen time=0
replace time=1 if year==2013
foreach y of global practices 	{
forv t=0/1 {
capture : svy : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
	
mat `y' = (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat plot = (`y')
else                          mat plot = (plot \ `y') 
						} 
mat coln plot = "Mean" "SE" "N" "Mean" "SE" "N"
mat li plot



**	Make Table of means at  hh level 
u "$out/CSA_practices_hh.dta", clear
qui : svyset psu [pw=panelweight], strata(strata) 

gl practices 	mintill crop_rot intleg_1 covercrop_any mulch  msd_assoc msd_residues assoc_residues cons_ag ///
		tree  intercrop_other  swc_mech swc_bio swc_any fert_org_use 	///
		burn   
	
gen time=0
replace time=1 if year==2013

foreach y of global practices 	{
forv t=0/1 {
capture : svy : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y' = (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat hh = (`y')
else                          mat hh = (hh \ `y') 
						} 
mat coln hh = "Mean" "SE" "N" "Mean" "SE" "N"
mat li hh


**	Export output

putexcel set "$tables/descriptive.xlsx", modify sheet("CSA", replace)
putexcel a1:o1 = "Summary Stats of Y variables at Plot and HH,  time by survey round", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("CSA")

putexcel b2:g2 = "Plot-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(plot), colnames vcenter hcenter 
putexcel b5:b20 c5:c20 e5:e20 f5:f20, nformat(0.000)
putexcel d5:d20 g5:g20, nformat(#,###)
putexcel a4:g4, border(bottom, thin)
putexcel a20:g20, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $practices''", vcenter 
	}
*
	

	
putexcel j2:o2 = "HH-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)
putexcel j3:l3 = "2010/11 IHS3", merge vcenter hcenter
putexcel m3:o3 = "2013 IHPS", merge vcenter hcenter
putexcel i3, border(top, thin)
putexcel j4 = mat(hh), colnames vcenter hcenter 
putexcel j5:j20 k5:k20 m5:m20 n5:n20, nformat(0.000)
putexcel l5:l20 o5:o20, nformat(#,###)
putexcel i4:o4, border(bottom, thin)
putexcel i20:o20, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel i`row' = "`: var label `: word `x' of $practices''", vcenter 
	}





***********************************DESCRIPTIVE BY REGION************************

**	Make Table of means at plot level

use "$out/CSA_practices_plot.dta", clear 
merge m:1 case_id year using "$out/MasterGeoVar_anto.dta", nogen force

qui : svyset psu[pw=panelweight], strata(strata) 

gl practices 	mintill crop_rot intleg_1 covercrop_any mulch  msd_assoc msd_residues assoc_residues cons_ag ///
		tree  intercrop_other  swc_mech swc_bio swc_any fert_org_use 	///
		burn   
	 
gen time=0
replace time=1 if year==2013
 


foreach y of global practices 	{
forv t=0/1 {
capture : svy : mean `y' if time==`t', over(region)
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y' = (tbl0[1, 1], tbl0[1, 2], tbl0[1, 3], tbl1[1, 1], tbl1[1, 2], tbl1[1, 3]) 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat plot = (`y')
else                          mat plot = (plot \ `y') 
						} 
mat coln plot =  "Northern" "Center" "Southern" "Northern" "Center" "Southern"
mat li plot



**	Make Table of means at  hh level 


use "$out/CSA_practices_hh.dta", clear 

qui : svyset psu[pw=panelweight], strata(strata) 

gl practices 	mintill crop_rot intleg_1 covercrop_any mulch  msd_assoc msd_residues assoc_residues cons_ag ///
		tree  intercrop_other  swc_mech swc_bio swc_any fert_org_use 	///
		burn   
	 
gen time=0
replace time=1 if year==2013
 


foreach y of global practices 	{
forv t=0/1 {
capture : svy : mean `y' if time==`t', over(region)
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y' = (tbl0[1, 1], tbl0[1, 2], tbl0[1, 3], tbl1[1, 1], tbl1[1, 2], tbl1[1, 3]) 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat hh = (`y')
else                          mat hh = (hh \ `y') 
						} 
mat coln hh =  "Northern" "Center" "Southern" "Northern" "Center" "Southern"
mat li hh

**	Export output

putexcel set "$tables/descriptive", modify sheet("CSA_reg", replace)
putexcel a1:o1 = "Summary Stats of Y variables at Plot and HH,  time by region and survey round", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("CSA_reg")

putexcel b2:g2 = "Plot-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(plot), colnames vcenter hcenter 
putexcel b5:b20 c5:c20 d5:d20 e5:e20 f5:f20 g5:g20, nformat(0.000)
putexcel a4:g4, border(bottom, thin)
putexcel a20:g20, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $practices''", vcenter 
	}
*
	

	
putexcel j2:o2 = "HH-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)
putexcel j3:l3 = "2010/11 IHS3", merge vcenter hcenter
putexcel m3:o3 = "2013 IHPS", merge vcenter hcenter
putexcel i3, border(top, thin)
putexcel j4 = mat(hh), colnames vcenter hcenter 
putexcel j5:j20 k5:k20 l5:l20 m5:m20 n5:n20 o5:o20, nformat(0.000)
putexcel i4:o4, border(bottom, thin)
putexcel i20:o20, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel i`row' = "`: var label `: word `x' of $practices''", vcenter 
	}




**Descriptive by region detailed. Note that if you want to present also the SE you need to calculate using the following command, in fact using subpop instead of over 
** allow us to take into account the fact that the sample was not designed within the population while using over the SE is calculated using the whole population
** On the contrary subpop take into accout only the subpop for calculating the SE


**	Make Table of means at plot level 
use "$out/CSA_practices_plot.dta", clear 
egen northern= anymatch(region), v(1)
egen central= anymatch(region), v(2)
egen southern= anymatch(region), v(3)


qui : svyset psu[pw=panelweight], strata(strata) 

gl practices 	mintill crop_rot intleg_1 covercrop_any mulch  msd_assoc msd_residues assoc_residues cons_ag ///
		tree  intercrop_other  swc_mech swc_bio swc_any fert_org_use 	///
		burn   
	 
gen time=0
replace time=1 if year==2013

foreach y of global practices 	{
forv t=0/1 {
capture : svy, subpop(northern): mean `y' if time==`t'
if _rc==0  mat tbl`t'1 = (r(table) \ e(_N)) 
else      mat tbl`t'1 = (., . ,. \ ., . ,. \ ., . ,. \ ., . ,.\ ., . ,. \ ., . ,. \ ., . ,.\ ., . ,. \ ., . ,. \ ., . ,. ) 
	}
	mat `y' = (tbl01[1..2, 1]', tbl01[10, 1]', tbl11[1..2, 1]', tbl11[10, 1]') 

*mat `y' = (tbl0[1..2, 1]' , tbl0[10, 1]' , tbl1[1..2, 1]' , tbl1[10, 1]'  \  tbl0[1..2, 2]' , tbl0[10, 2]' , tbl1[1..2, 2]' , tbl1[10, 2]'  \ tbl0[1..2, 3]' ,  tbl0[10, 3]'  ,  tbl1[1..2, 3]' , tbl1[10, 3]') 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat plot_nor = (`y')
else                          mat plot_nor = (plot_nor \ `y') 
						} 
mat coln plot_nor = "Mean" "SE" "N" "Mean" "SE" "N"
mat li plot_nor




foreach y of global practices 	{
forv t=0/1 {
capture : svy, subpop(central): mean `y' if time==`t'
if _rc==0  mat tbl`t'1 = (r(table) \ e(_N)) 
else      mat tbl`t'1 = (., . ,. \ ., . ,. \ ., . ,. \ ., . ,.\ ., . ,. \ ., . ,. \ ., . ,.\ ., . ,. \ ., . ,. \ ., . ,. ) 
	}
	mat `y' = (tbl01[1..2, 1]', tbl01[10, 1]', tbl11[1..2, 1]', tbl11[10, 1]') 

*mat `y' = (tbl0[1..2, 1]' , tbl0[10, 1]' , tbl1[1..2, 1]' , tbl1[10, 1]'  \  tbl0[1..2, 2]' , tbl0[10, 2]' , tbl1[1..2, 2]' , tbl1[10, 2]'  \ tbl0[1..2, 3]' ,  tbl0[10, 3]'  ,  tbl1[1..2, 3]' , tbl1[10, 3]') 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat plot_cen = (`y')
else                          mat plot_cen = (plot_cen \ `y') 
						} 
mat coln plot_cen = "Mean" "SE" "N" "Mean" "SE" "N"
mat li plot_cen




foreach y of global practices 	{
forv t=0/1 {
capture : svy, subpop(southern): mean `y' if time==`t'
if _rc==0  mat tbl`t'1 = (r(table) \ e(_N)) 
else      mat tbl`t'1 = (., . ,. \ ., . ,. \ ., . ,. \ ., . ,.\ ., . ,. \ ., . ,. \ ., . ,.\ ., . ,. \ ., . ,. \ ., . ,. ) 
	}
	mat `y' = (tbl01[1..2, 1]', tbl01[10, 1]', tbl11[1..2, 1]', tbl11[10, 1]') 

*mat `y' = (tbl0[1..2, 1]' , tbl0[10, 1]' , tbl1[1..2, 1]' , tbl1[10, 1]'  \  tbl0[1..2, 2]' , tbl0[10, 2]' , tbl1[1..2, 2]' , tbl1[10, 2]'  \ tbl0[1..2, 3]' ,  tbl0[10, 3]'  ,  tbl1[1..2, 3]' , tbl1[10, 3]') 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat plot_sur = (`y')
else                          mat plot_sur = (plot_sur \ `y') 
						} 
mat coln plot_sur = "Mean" "SE" "N" "Mean" "SE" "N"
mat li plot_sur

mat blank= (. , . , . , . ,  . , .)
mat plot_reg=  blank \ plot_nor \ blank\  plot_cen \ blank \  plot_sur 

mat li plot_reg




**	Make Table of means at  hh level 

use "$out/CSA_practices_hh.dta", clear 
egen northern= anymatch(region), v(1)
egen central= anymatch(region), v(2)
egen southern= anymatch(region), v(3)

qui : svyset psu[pw=panelweight], strata(strata) 

gl practices 	mintill crop_rot intleg_1 covercrop_any mulch  msd_assoc msd_residues assoc_residues cons_ag ///
		tree  intercrop_other  swc_mech swc_bio swc_any fert_org_use 	///
		burn   
	 
gen time=0
replace time=1 if year==2013


foreach y of global practices 	{
forv t=0/1 {
capture : svy, subpop(northern) : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
	}
mat `y' = (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat hh_nor = (`y')
else                          mat hh_nor = (hh_nor \ `y') 
						} 
mat coln hh_nor = "Mean" "SE" "N" "Mean" "SE" "N"
mat li hh_nor




foreach y of global practices 	{
forv t=0/1 {
capture : svy, subpop(central) : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
	}
mat `y' = (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat hh_cen = (`y')
else                          mat hh_cen = (hh_cen \ `y') 
						} 
mat coln hh_cen = "Mean" "SE" "N" "Mean" "SE" "N"
mat li hh_cen


 

foreach y of global practices	{
forv t=0/1 {
capture : svy, subpop(southern) : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
	}
mat `y' = (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat hh_sur = (`y')
else                          mat hh_sur = (hh_sur \ `y') 
						} 
mat coln hh_sur = "Mean" "SE" "N" "Mean" "SE" "N"
mat li hh_sur

mat blank= (. , . , . , . ,  . , .)
mat hh_reg= blank \ hh_nor \ blank \   hh_cen\ blank \ hh_sur 

mat li hh_reg



**	Export output

putexcel set "$tables/descriptive.xlsx", modify sheet("CSA_reg_det", replace)
putexcel a1:o1 = "Summary Stats of Y variables at Plot and HH,  time by region and survey round", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("CSA_reg_det")

putexcel b2:g2 = "Plot-Level Means by region Survey Round", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel b4="Mean" c4="SE" d4="N" , vcenter hcenter
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel e4="Mean" f4="SE" g4="N" , vcenter hcenter
putexcel a3, border(top, thin)
putexcel b5 = mat(plot_reg),  vcenter hcenter 
putexcel b6:b55 c6:c43 e6:e55 f6:f55, nformat(0.000)
putexcel d6:d55 g6:g55, nformat(#,###)
putexcel a4:g4, border(bottom, thin)
putexcel a55:g55, border(bottom, thin)
putexcel a5:g5    = "Northern" , merge vcenter hcenter bold border(all, thin)
putexcel a22:g22  = "Central" , merge vcenter hcenter bold border(all, thin)
putexcel a39:g39  = "Southern" , merge vcenter hcenter bold border(all, thin)

loc seed = 5
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $practices''", vcenter 
	}
	loc seed = 22
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $practices''", vcenter 
	}
	loc seed = 39
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $practices''", vcenter 
	}
*

putexcel j2:o2 = "HH-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)
putexcel j3:l3 = "2010/11 IHS3", merge vcenter hcenter
putexcel j4="Mean" k4="SE" l4="N" , vcenter hcenter
putexcel m3:o3 = "2013 IHPS", merge vcenter hcenter
putexcel m4="Mean" n4="SE" o4="N" , vcenter hcenter
putexcel i3, border(top, thin)
putexcel j5 = mat(hh_reg),  vcenter hcenter 
putexcel j6:j55 k6:k55 m6:m55 n6:n55, nformat(0.000)
putexcel l6:l55 o6:o55, nformat(#,###)
putexcel i4:o4, border(bottom, thin)
putexcel i55:o55, border(bottom, thin)
putexcel i5:o5    = "Northern" , merge vcenter hcenter bold border(all, thin)
putexcel i22:o22  = "Central" , merge vcenter hcenter bold border(all, thin)
putexcel i39:o39  = "Southern" , merge vcenter hcenter bold border(all, thin)


loc seed = 5
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel i`row' = "`: var label `: word `x' of $practices''", vcenter 
	}
	loc seed = 22
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel i`row' = "`: var label `: word `x' of $practices''", vcenter 
	}
	loc seed = 39
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel i`row' = "`: var label `: word `x' of $practices''", vcenter 
	}
*





***********************************DESCRIPTIVE BY AGROECOLOGICAL************************

**	Make Table of means at plot level

use "$out/CSA_practices_plot.dta", clear 
merge m:1 case_id year using "$out/MasterGeoVar_anto.dta", nogen force keepusing(ssa_aez09)

qui : svyset psu[pw=panelweight], strata(strata) 

gl practices 	mintill crop_rot intleg_1 covercrop_any mulch  msd_assoc msd_residues assoc_residues cons_ag ///
		tree  intercrop_other  swc_mech swc_bio swc_any fert_org_use 	///
		burn   
	 
gen time=0
replace time=1 if year==2013
 


foreach y of global practices 	{
forv t=0/1 {
capture : svy : mean `y' if time==`t', over(ssa_aez09)
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y' = (tbl0[1, 1], tbl0[1, 2], tbl0[1, 3], tbl0[1, 4],  tbl1[1, 1], tbl1[1, 2], tbl1[1, 3], tbl1[1, 4]) 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat plot = (`y')
else                          mat plot = (plot \ `y') 
						} 
mat coln plot =  "Warm/semiarid" "Warm/subhumid" "Cool/semiarid" "Cool/subhumid""Warm/semiarid" "Warm/subhumid" "Cool/semiarid" "Cool/subhumid"
mat li plot



**	Make Table of means at  hh level 


use "$out/CSA_practices_hh.dta", clear 
merge 1:1 case_id year using "$out/MasterGeoVar_anto.dta", nogen force keepusing(ssa_aez09)

qui : svyset psu[pw=panelweight], strata(ssa_aez09) 

gl practices 	mintill crop_rot intleg_1 covercrop_any mulch  msd_assoc msd_residues assoc_residues cons_ag ///
		tree  intercrop_other  swc_mech swc_bio swc_any fert_org_use 	///
		burn   
	 
gen time=0
replace time=1 if year==2013
 


foreach y of global practices 	{
forv t=0/1 {
capture : svy : mean `y' if time==`t', over(ssa_aez09)
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y' = (tbl0[1, 1], tbl0[1, 2], tbl0[1, 3], tbl0[1, 4],  tbl1[1, 1], tbl1[1, 2], tbl1[1, 3], tbl1[1, 4]) 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $practices'"  mat hh = (`y')
else                          mat hh = (hh \ `y') 
						} 
mat coln hh = "Warm/semiarid" "Warm/subhumid" "Cool/semiarid" "Cool/subhumid""Warm/semiarid" "Warm/subhumid" "Cool/semiarid" "Cool/subhumid"

mat li hh

**	Export output

putexcel set "$tables/descriptive", modify sheet("CSA_AEZ", replace)
putexcel a1:o1 = "Summary Stats of Y variables at Plot and HH,  time by region and survey round", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("CSA_AEZ")

putexcel b2:i2 = "Plot-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:e3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel f3:i3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(plot), colnames vcenter hcenter 
putexcel b5:b20 c5:c20 d5:d20 e5:e20 f5:f20 g5:g20 h5:h20 i5:i20, nformat(0.000)
putexcel a4:i4, border(bottom, thin)
putexcel a20:i20, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $practices''", vcenter 
	}
*
	

	
putexcel k2:r2 = "HH-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)
putexcel k3:n3 = "2010/11 IHS3", merge vcenter hcenter
putexcel m3:r3 = "2013 IHPS",  vcenter hcenter
putexcel j3, border(top, thin)
putexcel k4 = mat(hh), colnames vcenter hcenter 
putexcel k5:k20 l5:l20 m5:m20 n5:n20 o5:o20 p5:p20 q5:q20 r5:r20, nformat(0.000)
putexcel j4:r4, border(bottom, thin)
putexcel j20:r20, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $practices' {
loc row = `x' + `seed'
qui : putexcel i`row' = "`: var label `: word `x' of $practices''", vcenter 
	}



















********************************************************************************
********************************************************************************
**	Adoption/Disadoption Matrices 
********************************************************************************
********************************************************************************
set more off
u "$out/CSA_practices_hh.dta", clear
qui : svyset psu [pw=panelweight], strata(strata) 
	 
gen time=0
replace time=1 if year==2013
xtset hh time	//-> we are restricted to a balanced panel 
	
gl vars   intleg_1 intercrop_other  tree
isid case_id time
keep case_id psu panelweight strata time $vars 

preserve
reshape wide $vars , i(case_id psu panelweight strata) j(time) 
 
*loc var swc_mech 
foreach var of global vars  {
svy : tabulate `var'0 `var'1, row obs cellwidth(18) format(%12.0g)
matrix freq = e(N_pop)*e(Prop)

mata: st_matrix("freq1"   , (st_matrix("freq")  :/  rowsum(st_matrix("freq")) )) 
mata: st_matrix("freq_cs1", (st_matrix("freq") :/ sum(st_matrix("freq"))))
mata: st_matrix("freq_cs2", colsum(st_matrix("freq_cs1")))
mat A= freq1 \ freq_cs2
mata:  st_matrix("freq_rs" ,  rowsum(st_matrix("A")))
mat B= A , freq_rs
mat list B


mata:  st_matrix("Obs_rs" ,  rowsum(st_matrix("e(Obs)")))
mat C= e(Obs), Obs_rs
mata:  st_matrix("D" ,   colsum(st_matrix("C")))
mat E= C\D

mat Z= E,B
mat list Z


mat `var'wgt = (Z[1, 1]\Z[1, 4]) , (Z[1, 2]\Z[1, 5]) , (Z[1, 3]\Z[1, 6] ) \  (Z[2, 1]\Z[2, 4]) , (Z[2, 2]\Z[2, 5]) , (Z[2, 3]\Z[2, 6] ) \ (Z[3, 1]\Z[3, 4]) , (Z[3, 2]\Z[3, 5]) , (Z[3, 3]\Z[3, 6] )
mat coln `var'wgt = "No"  "Yes"  "Total"
mat rown `var'wgt = "No" "No %" "Yes" "Yes %" "Total" "Total %" 
mat li `var'wgt
}


restore

putexcel set "$tables/descriptive.xlsx", modify  sheet("transition_matrices", replace)



qui: putexcel a1 = "Household Transition Matrices", bold underline
loc r1 = -9
foreach x of global vars {
loc r1 = `r1' + 12
loc r2 = `r1' + 1
loc r3 = `r1' + 2
loc r4 = `r1' + 3
loc r5 = `r1' + 4
loc r6 = `r1' + 5
loc r7 = `r1' + 6 
loc r8 = `r1' + 7 
loc r9 = `r1' + 8 

dis "`: var label `x''"
qui: putexcel b`r1':e`r1' = "`: var label `x''", merge txtwrap vcenter hcenter bold
qui: putexcel a`r4':a`r9'  = "2010", merge txtrotate(255) txtwrap vcenter hcenter 
qui: putexcel b`r3':b`r6',  border(right, thin)
qui: putexcel c`r2':e`r2'  = "2013", merge vcenter hcenter
qui: putexcel b`r3':e`r3',   border(bottom, thin)
qui: putexcel b`r4':b`r5'= "No", merge 
qui: putexcel b`r6':b`r7'= "Yes", merge 
qui: putexcel b`r8':b`r9'= "Total", merge 
qui : putexcel b`r3'= mat(`x'wgt), names vcenter hcenter txtwrap  overwritefmt
qui: putexcel b`r7':e`r7',   border(bottom, thin)
qui: putexcel c`r5':d`r5' c`r7':d`r7' c`r9':d`r9' ,  nformat(0.000) overwritefmt
}




*********************************************************************************
*******************************************HH SOCIODEMOGRAPHIC***********************



set more off
use "$out\final_dataset.dta", clear
qui : svyset psu [pw=panelweight], strata(strata)



gl  HH  hhsize  agehead femhead  educhigh depratio   ///
				 landown  tlu_oxen_cattle ///
				 region_dummies1 region_dummies2 region_dummies3 




qui : svyset psu [pw=panelweight], strata(strata)
est clear
foreach y of global HH	{
forv t=0/1 {
qui: capture: svy : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
}
*increase the . according the number o columns putting a coma between the points
mat `y' = (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 
mat rown `y' = `y'
if "`y'"=="`: word 1 of $HH'"  mat hh = (`y')
else                          mat hh = (hh \ `y') 
						} 
mat coln hh = "Mean" "SE" "N" "Mean" "SE" "N"
mat li hh




*Export results	
putexcel set "$tables/descriptive.xlsx", modify sheet("Sociodem", replace)
putexcel a1:d1 = "Summary Stats of control practices in 2013/2014", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("Sociodem")

putexcel b2:g2 = "Household-Level Means", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(hh), colnames vcenter hcenter 
putexcel b5:b21 c5:c14 e5:e14 f5:f14, nformat(0.000)
putexcel d5:d14 g5:g14, nformat(#,###)
putexcel a4:g4, border(bottom, thin)
putexcel a14:g14, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $HH' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $HH''", vcenter 
	}
*
	

	
******************************COMMUNITY CHARACTERISTICS***********************
set more off
use "$out\final_dataset.dta", clear
qui : svyset psu [pw=panelweight], strata(strata)

		
rename shcredit_MFIB_nbHHinEA shcredit_inEA
rename shext_Gov_nbHHinEA shext_inEA		
		
		
global CMM		irrigation_scheme    ///
				 shext_inEA shcredit_inEA  sh_fisp_EA
				 
qui : svyset psu [pw=panelweight], strata(strata)
est clear
foreach y of global CMM	{
forv t=0/1 {
qui: capture: svy : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
}
*increase the . according the number o columns putting a coma between the points
mat `y' = (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 
mat rown `y' = `y'
if "`y'"=="`: word 1 of $CMM'"  mat cmm = (`y')
else                          mat cmm = (cmm \ `y') 
						} 
mat coln cmm = "Mean" "SE" "N" "Mean" "SE" "N"
mat li cmm




*Export results	
putexcel set "$tables/descriptive.xlsx", modify sheet("Community", replace)
putexcel a1:d1 = "Summary Stats of control practices in 2013/2014", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("Community")

putexcel b2:g2 = "Household-Level Means", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(cmm), colnames vcenter hcenter 
putexcel b5:b10 c5:c10 e5:e10 f5:f10, nformat(0.000)
putexcel d5:d10 g5:g10, nformat(#,###)
putexcel a4:g4, border(bottom, thin)
putexcel a10:g10, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $CMM' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $CMM''", vcenter 
	}


******************************************YIELD
set more off
use "$out\final_dataset.dta", clear
qui : svyset psu [pw=panelweight], strata(strata)




global Yield "value_total_yield weighted_yield"
est clear
foreach y of global Yield	{
forv t=0/1 {
qui: capture: svy : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
}
*increase the . according the number o columns putting a coma between the points
mat `y' = (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 
mat rown `y' = `y'
if "`y'"=="`: word 1 of $Yield'"  mat hh_yield = (`y')
else                          mat hh_yield = (hh_yield \ `y') 
						} 
mat coln hh_yield = "Mean" "SE" "N" "Mean" "SE" "N"
mat li hh_yield


*Export results	
putexcel set "$tables/descriptive.xlsx", modify sheet("Yield", replace)
putexcel a1:d1 = "Summary Stats of control practices in 2013/2014", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("Yield")

putexcel b2:g2 = "Household-Level Means", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(hh_yield), colnames vcenter hcenter 
putexcel b5:b6 c5:c6 e5:e6 f5:f6, nformat(0.000)
putexcel d5:d6 g5:g6, nformat(#,###)
putexcel a4:g4, border(bottom, thin)
putexcel a6:g6, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $Yield' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $Yield''", vcenter 
	}

	
*******************************************INCOME AND CONSUMPTION
set more off
use "$out\final_dataset.dta", clear
qui : svyset psu [pw=panelweight], strata(strata)
global Welfare " netincome consumption_pc poverty  "

est clear
foreach y of global Welfare	{
forv t=0/1 {
qui: capture: svy : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
}
*increase the . according the number o columns putting a coma between the points
mat `y' = (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 
mat rown `y' = `Welfare'
if "`y'"=="`: word 1 of $Welfare'"  mat hh_wel = (`y')
else                          mat hh_wel = (hh_wel \ `y') 
						} 
mat coln hh_wel = "Mean" "SE" "N" "Mean" "SE" "N"
mat li hh_wel


*Export results	
putexcel set "$tables/descriptive.xlsx", modify sheet("Welfare", replace)
putexcel a1:d1 = "Summary Stats of control practices in 2013/2014", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("Welfare")

putexcel b2:g2 = "Household-Level Means", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(hh_wel), colnames vcenter hcenter 
putexcel b5:b7 c5:c7 e5:e7 f5:f7, nformat(0.000)
putexcel d5:d7 g5:g7, nformat(#,###)
putexcel a4:g4, border(bottom, thin)
putexcel a7:g7, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $Welfare' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $Welfare''", vcenter 
	}
*
	
	
	
*******************************************FOOD SECURITY
set more off
use "$out\final_dataset.dta", clear
qui : svyset psu [pw=panelweight], strata(strata)






global Food_security "food_pc caloric_intake"
est clear
foreach y of global Food_security	{
forv t=0/1 {
qui: capture: svy : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
}
*increase the . according the number o columns putting a coma between the points
mat `y' = (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 
mat rown `y' = `Food_security'
if "`y'"=="`: word 1 of $Food_security'"  mat hh_fs = (`y')
else                          mat hh_fs = (hh_fs \ `y') 
						} 
mat coln hh_fs = "Mean" "SE" "N" "Mean" "SE" "N"
mat li hh_fs


*Export results	
putexcel set "$tables/descriptive.xlsx", modify sheet("Food_sec", replace)
putexcel a1:d1 = "Summary Stats of control practices in 2013/2014", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("Food_sec")

putexcel b2:g2 = "Household-Level Means", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(hh_fs), colnames vcenter hcenter 
putexcel b5:b6 c5:c6 e5:e8 f5:f6, nformat(0.000)
putexcel d5:d8 g5:g6, nformat(#,###)
putexcel a4:g4, border(bottom, thin)
putexcel a6:g6, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $Food_security' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $Food_security''", vcenter 
	}




*******************************************CLIM RISK
set more off
use "$out\final_dataset.dta", clear
qui : svyset psu [pw=panelweight], strata(strata)

bys ea_id: gen tmax28=mean_tmax_season>28
lab var tmax28 "Max temp 28°C (%)" 



global Clim_risk "neg_shock_prob pos_shock_prob covrain_lt  false_onset_prob  mean_totrain_lt mean_rain_season tmax28 false_onset"
est clear
foreach y of global Clim_risk	{
forv t=0/1 {
qui: capture: svy : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
}
*increase the . according the number o columns putting a coma between the points
mat `y' = (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 
mat rown `y' = `Clim_risk'
if "`y'"=="`: word 1 of $Clim_risk'"  mat hh_cr = (`y')
else                          mat hh_cr = (hh_cr \ `y') 
						} 
mat coln hh_cr = "Mean" "SE" "N" "Mean" "SE" "N"
mat li hh_cr

	
*Export results	
putexcel set "$tables/descriptive.xlsx", modify sheet("Clim_risk", replace)
putexcel a1:d1 = "Summary Stats of control practices in 2013/2014", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("Clim_risk")

putexcel b2:g2 = "Household-Level Means", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(hh_cr), colnames vcenter hcenter 
putexcel b5:b16 c5:c16 e5:e16 f5:f16, nformat(0.000)
putexcel d5:d16 g5:g16, nformat(#,###)
putexcel a4:g4, border(bottom, thin)
putexcel a16:g16, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $Clim_risk' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $Clim_risk''", vcenter 
	}


	
	
 ***************by region************************


use "$out/final_dataset.dta", clear

qui : svyset psu[pw=panelweight], strata(strata) 

global Clim_risk "neg_shock_prob pos_shock_prob covrain_lt  false_onset_prob  mean_totrain_lt"


foreach y of global Clim_risk 	{
forv t=0/1 {
capture : svy : mean `y' if time==`t', over(region)
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y' = (tbl0[1, 1], tbl0[1, 2], tbl0[1, 3], tbl1[1, 1], tbl1[1, 2], tbl1[1, 3]) 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $Clim_risk'"  mat cr = (`y')
else                          mat cr = (cr \ `y') 
						} 
mat coln cr =  "Northern" "Center" "Southern" "Northern" "Center" "Southern"
mat li cr

**	Export output

putexcel set "$tables/descriptive", modify sheet("Clim_risk_reg", replace)
putexcel a1:o1 = "Summary Stats of Y variables at Plot and HH,  time by region and survey round", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("Clim_risk_reg")

putexcel b2:g2 = "Plot-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(cr), colnames vcenter hcenter 
putexcel b5:b16 c5:c16 d5:d16 e5:e16 f5:f16 g5:g16, nformat(0.000)
putexcel a4:g4, border(bottom, thin)
putexcel a16:g16, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $Clim_risk' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $Clim_risk''", vcenter 
	}
	

	
	
	
	
	
	
*******************************************************************************	
**********************************OTHER DESCRIPTIVES AND MAPS******************	
*******************************************************************************	
	
	
	
	
	

*********************************CROP (plot level)
use "$out/CSA_practices_plot", clear 
encode plotid, gen(plot_id)
drop _merge
tempfile temp
save `temp'
use "$out\total_yield.dta", clear
tostring case_id,format(%17.0g) replace force
merge 1:m case_id plot_id year using `temp',  force keep(3) nogen

qui : svyset psu[pw=panelweight], strata(strata) 

global crop "crop_1 crop_2 crop_3 crop_4 crop_5 crop_6 crop_7 crop_8 crop_9 crop_10 crop_11 crop_12 crop_13 crop_14 crop_15 crop_16 crop_17 crop_18 crop_19 crop_20 crop_21 crop_22 crop_23 crop_24 crop_25 crop_26 crop_27 crop_28 crop_29 crop_30 crop_31 crop_32 crop_33 crop_34 crop_35 crop_36 crop_37 crop_38 crop_39 crop_40 crop_41 crop_42 crop_43 crop_44 crop_45 crop_46 crop_47 "

gen time=0
replace time=1 if year==2013
foreach y of global crop	{
forv t=0/1 {
capture : svy : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y'= (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 

mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $crop'"  mat crop = (`y')
else                          mat crop = (crop \ `y') 
						} 
mat coln crop = "Mean" "SE" "N" "Mean" "SE" "N"
mat li crop

**	Export output

putexcel set "$tables/descriptive.xlsx", modify sheet("crop", replace)
putexcel a1:g1 = "Summary Stats of Crop at Plot level,  time by survey round", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("crop")

putexcel b2:g2 = "Plot-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(crop), colnames vcenter hcenter 
putexcel b5:b51 c5:c51 e5:e51 f5:f51, nformat(0.000)
putexcel d5:d51 g5:g51, nformat(#,###)
putexcel a4:g4, border(bottom, thin)
putexcel a51:g51, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $crop' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $crop''", vcenter 
	}
*


 ***************by region************************


use "$out/CSA_practices_plot", clear 
encode plotid, gen(plot_id)
drop _merge
tempfile temp
save `temp'
use "$out\total_yield.dta", clear
tostring case_id,format(%17.0g) replace force
merge 1:m case_id plot_id year using `temp',  force keep(3) nogen

qui : svyset psu[pw=panelweight], strata(strata) 

global crop "crop_1 crop_2 crop_3 crop_4 crop_5 crop_6 crop_7 crop_8 crop_9 crop_10 crop_11 crop_12 crop_13 crop_14 crop_15 crop_16 crop_17 crop_18 crop_19 crop_20 crop_21 crop_22 crop_23 crop_24 crop_25 crop_26 crop_27 crop_28 crop_29 crop_30 crop_31 crop_32 crop_33 crop_34 crop_35 crop_36 crop_37 crop_38 crop_39 crop_40 crop_41 crop_42 crop_43 crop_44 crop_45 crop_46 crop_47 "

gen time=0
replace time=1 if year==2013
foreach y of global crop 	{
forv t=0/1 {
capture : svy : mean `y' if time==`t', over(region)
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y' = (tbl0[1, 1], tbl0[1, 2], tbl0[1, 3], tbl1[1, 1], tbl1[1, 2], tbl1[1, 3]) 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $crop'"  mat crop = (`y')
else                          mat crop = (crop \ `y') 
						} 
mat coln crop =  "Northern" "Center" "Southern" "Northern" "Center" "Southern"
mat li crop

**	Export output

putexcel set "$tables/descriptive", modify sheet("crop_reg", replace)
putexcel a1:o1 = "Summary Stats of Y variables at Plot and HH,  time by region and survey round", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("crop_reg")

putexcel b2:g2 = "Plot-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(crop), colnames vcenter hcenter 
putexcel b5:b51 c5:c51 d5:d51 e5:e51 f5:f51 g5:g51, nformat(0.000)
putexcel a4:g4, border(bottom, thin)
putexcel a51:g51, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $crop' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $crop''", vcenter 
	}


	
	
*******************************TREES AND PERENNIAL (HH LEVEL)

********************************************************general
use "$out/final_dataset.dta", clear

qui : svyset psu[pw=panelweight], strata(strata) 

global tree_per "tree_per_1 tree_per_2 tree_per_3 tree_per_4 tree_per_5 tree_per_6 tree_per_7 tree_per_8 tree_per_9 tree_per_10 tree_per_11 tree_per_12 tree_per_13 tree_per_14 tree_per_15 tree_per_16"


foreach y of global tree_per	{
forv t=0/1 {
capture : svy : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y'= (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 

mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $tree_per'"  mat tree = (`y')
else                          mat tree = (tree \ `y') 
						} 
mat coln tree = "Mean" "SE" "N" "Mean" "SE" "N"
mat li tree

**	Export output

putexcel set "$tables/descriptive.xlsx", modify sheet("tree", replace)
putexcel a1:g1 = "Summary Stats of Trees and Perennial Crops at HH level,  time by survey round", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("tree")

putexcel b2:g2 = "Plot-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(tree), colnames vcenter hcenter 
putexcel b5:b20 c5:c20 e5:e20 f5:f20, nformat(0.000)
putexcel d5:d20 g5:g20, nformat(#,###)
putexcel a4:g4, border(bottom, thin)
putexcel a20:g20, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $tree_per' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $tree_per''", vcenter 
	}

	
 ***************by region************************


use "$out/final_dataset.dta", clear

qui : svyset psu[pw=panelweight], strata(strata) 

global tree_per "tree_per_1 tree_per_2 tree_per_3 tree_per_4 tree_per_5 tree_per_6 tree_per_7 tree_per_8 tree_per_9 tree_per_10 tree_per_11 tree_per_12 tree_per_13 tree_per_14 tree_per_15 tree_per_16"


foreach y of global tree_per 	{
forv t=0/1 {
capture : svy : mean `y' if time==`t', over(region)
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y' = (tbl0[1, 1], tbl0[1, 2], tbl0[1, 3], tbl1[1, 1], tbl1[1, 2], tbl1[1, 3]) 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $tree_per'"  mat tree = (`y')
else                          mat tree = (tree \ `y') 
						} 
mat coln tree =  "Northern" "Center" "Southern" "Northern" "Center" "Southern"
mat li tree

**	Export output

putexcel set "$tables/descriptive", modify sheet("tree_reg", replace)
putexcel a1:o1 = "Summary Stats of Y variables at Plot and HH,  time by region and survey round", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("tree_reg")

putexcel b2:g2 = "Plot-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(tree), colnames vcenter hcenter 
putexcel b5:b20 c5:c20 d5:d20 e5:e20 f5:f20 g5:g20, nformat(0.000)
putexcel a4:g4, border(bottom, thin)
putexcel a20:g20, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $tree_per' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $tree_per''", vcenter 
	}
	
	
	

	
	
*******************************LIVESTOCK (HH LEVEL)
****************************************national
use "$out/final_dataset.dta", clear

qui : svyset psu[pw=panelweight], strata(strata) 

global lvstk "TLU_cattle TLU_oxen TLU_pigs TLU_sheep TLU_goats TLU_chicken TLU_donkeyhorses TLU_otheranim TLU_total"


foreach y of global lvstk	{
forv t=0/1 {
capture : svy : mean `y' if time==`t'
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y'= (tbl0[1..2, 1]', tbl0[10, 1]', tbl1[1..2, 1]', tbl1[10, 1]') 

mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $lvstk'"  mat lvstk = (`y')
else                          mat lvstk = (lvstk \ `y') 
						} 
mat coln lvstk = "Mean" "SE" "N" "Mean" "SE" "N"
mat li lvstk

**	Export output

putexcel set "$tables/descriptive.xlsx", modify sheet("livestock", replace)
putexcel a1:g1 = "Summary Stats of Trees and Perennial Crops at HH level,  time by survey round", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("livestock")

putexcel b2:g2 = "Plot-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(lvstk), colnames vcenter hcenter 
putexcel b5:b13 c5:c13 e5:e13 f5:f13, nformat(0.000)
putexcel d5:d13 g5:g13, nformat(#,###)
putexcel a4:g4, border(bottom, thin)
putexcel a13:g13, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $lvstk' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $lvstk''", vcenter 
	}
	
 ***************by region************************


use "$out/final_dataset.dta", clear

qui : svyset psu[pw=panelweight], strata(strata) 

global lvstk "TLU_cattle TLU_oxen TLU_pigs TLU_sheep TLU_goats TLU_chicken TLU_donkeyhorses TLU_otheranim TLU_total"


foreach y of global lvstk 	{
forv t=0/1 {
capture : svy : mean `y' if time==`t', over(region)
if _rc==0 mat tbl`t' = (r(table) \ e(_N))
else      mat tbl`t' = (. \ . \ .\ .\ .\ . \ . \ . \ . \ . \ .)
*increase the . according the number o columns putting a coma between the points
	}
mat `y' = (tbl0[1, 1], tbl0[1, 2], tbl0[1, 3], tbl1[1, 1], tbl1[1, 2], tbl1[1, 3]) 
mat rown `y' = "`y'"
if "`y'"=="`: word 1 of $lvstk'"  mat lvstk = (`y')
else                          mat lvstk = (lvstk \ `y') 
						} 
mat coln lvstk =  "Northern" "Center" "Southern" "Northern" "Center" "Southern"
mat li lvstk

**	Export output

putexcel set "$tables/descriptive", modify sheet("livestock_reg", replace)
putexcel a1:o1 = "Summary Stats of Y variables at Plot and HH,  time by region and survey round", merge vcenter hcenter  bold underline
putexcel set "$tables/descriptive.xlsx", modify sheet("livestock_reg")

putexcel b2:g2 = "Plot-Level Means by Survey Round", merge vcenter hcenter bold border(bottom, thin)  
putexcel b3:d3 = "2010/11 IHS3", merge vcenter hcenter 
putexcel e3:g3 = "2013 IHPS", merge vcenter hcenter
putexcel a3, border(top, thin)
putexcel b4 = mat(lvstk), colnames vcenter hcenter 
putexcel b5:b13 c5:c13 d5:d13 e5:e13 f5:f13 g5:g13, nformat(0.000)
putexcel a4:g4, border(bottom, thin)
putexcel a13:g13, border(bottom, thin)

loc seed = 4
forv x =1/`: word count $lvstk' {
loc row = `x' + `seed'
qui : putexcel a`row' = "`: var label `: word `x' of $lvstk''", vcenter 
	}
*
